IF EXISTS (
	SELECT *
	FROM sysobjects
	WHERE id = object_id(N'dbo.spFdsNHumanReviewInject_Reviews')
	AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

	BEGIN
		
		PRINT 'Dropping spFdsNHumanReviewInject_Reviews'
        DROP PROCEDURE dbo.spFdsNHumanReviewInject_Reviews
        
	END

GO
	
/*******************************************************************************
* Name:					spFdsNHumanReviewInject_Reviews
* Purpose:				Injects new RevReview records from the staging table
*						and updates the staging table with the newly inserted
*						ids.  (also creates the review container, if
*						necessary).
*
*
* ERRORS USED			Description
* -------------			--------------------------------------------------------
* 
*
* RETURN VALUE
* Value    				Description					
* --------------		--------------------------------------------------------
* @_exitStatus		An exit status of either true or false
********************************************************************************/

CREATE PROCEDURE spFdsNHumanReviewInject_Reviews

AS

BEGIN

	SET NOCOUNT ON

	DECLARE @_stagedReviewId			AS INTEGER
	DECLARE @_currentProtocolId			AS INTEGER
	DECLARE @_protocolEntityTypeId		AS INTEGER
	
	DECLARE @_TRUE AS INTEGER
	SET @_TRUE = 1
	
	DECLARE @_SUCCESS AS BIT
	DECLARE @_FAILURE AS BIT
	SET @_SUCCESS = 1
	SET @_FAILURE = 0
	SET @_protocolEntityTypeId = 1
	
	
	
	-- select the set of new reviews and open up a cursor on that data
	DECLARE cursorNewReviewIds CURSOR LOCAL FORWARD_ONLY FOR
		SELECT		DISTINCT(ReviewStaging.Id), PrtProtocolId
		FROM		FdsProtocolStagingProtocolReview ReviewStaging
		INNER JOIN	FdsProtocolStagingBoardReview board
			ON		board.RexReviewId = ReviewStaging.RexReviewId
			AND		board.RexProtocolId = ReviewStaging.RexProtocolId
		INNER JOIN	FdsProtocolStagingBoardReviewDetail detail
			ON		detail.RexProtocolId = board.RexProtocolId
			AND		detail.RexReviewId = board.RexReviewId
			AND		detail.RexMailGroupId = board.RexMailGroupId
		WHERE		ReviewStaging.RevReviewId IS NULL

		
	BEGIN TRANSACTION spFdsNHumanReviewInject_Reviews
	
	-- iterate over each new review inserting the data to the review
	-- table and updating the staging table with the new review ID
	OPEN cursorNewReviewIds
	FETCH NEXT FROM cursorNewReviewIds INTO @_stagedReviewId, @_currentProtocolId
	WHILE @@FETCH_STATUS = 0
  	BEGIN
  	
  		-- generate a new container if this is the first review for this protocol
  		IF ((SELECT	RevReviewContainerId 
  			 FROM	FdsProtocolStagingProtocolReview
  			 WHERE	Id = @_stagedReviewId) IS NULL )
  			 
  		BEGIN
  		
  		  	DECLARE @_newReviewContainerId AS INTEGER
  		
  			-- create a new container
  			EXEC @_newReviewContainerId = spRevNCreateReviewContainer @_protocolEntityTypeId
  			
  			-- update the protocol with the review container just generated
  			UPDATE	PrtProtocol
  			SET		RevReviewContainerId = @_newReviewContainerId
  			WHERE	Id = @_currentProtocolId
  			
  			-- update the staging table with the review container ID.
  			-- IMPORTANT: update the container id for ALL reviews for a
  			-- given protocol + job in the staging table (otherwise, it will generate a
  			-- new review container for each review)
			UPDATE	FdsProtocolStagingProtocolReview
  			SET		RevReviewContainerId = @_newReviewContainerId
  			WHERE	FdsProtocolStagingProtocolReview.PrtProtocolId = @_currentProtocolId
  				
				  		
  		END
		      	
		-- insert the new review data
		INSERT INTO RevReview (
				RevReviewContainerId,
				RevReviewTypeId,
				ReviewRequestedDate,
				AlternateReviewId )
		SELECT	ReviewStaging.RevReviewContainerId
				,ReviewStaging.RevReviewTypeId
				,ReviewStaging.ReviewRequestedDate
				,ReviewStaging.AlternateReviewId
		FROM	FdsProtocolStagingProtocolReview ReviewStaging
		WHERE	ReviewStaging.Id = @_stagedReviewId
			
		
		IF @@ERROR > 0 GOTO ERROR_HANDLER
		
		
		-- update staging table with new review ID
		UPDATE		FdsProtocolStagingProtocolReview
  		SET			RevReviewId = SCOPE_IDENTITY()
  		WHERE		Id = @_stagedReviewId
		
      	-- get next review ID from list
		FETCH NEXT FROM cursorNewReviewIds INTO @_stagedReviewId, @_currentProtocolId
	
   	END
   	
   	-- close and deallocate cursor
	CLOSE cursorNewReviewIds
  	DEALLOCATE cursorNewReviewIds
  	
  	COMMIT TRANSACTION spFdsNHumanReviewInject_Reviews

  	SET NOCOUNT OFF
  	
	RETURN @_SUCCESS

ERROR_HANDLER:
	BEGIN
		ROLLBACK TRANSACTION spFdsNHumanReviewInject_Reviews
		
		RETURN @_FAILURE
	END
END 